package com.project.dao.impl;

import com.project.bean.LinkBean;
import com.project.dao.ILinkDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

//通讯录持久接口实现类
public class LinkDaoImpl implements ILinkDao {
    @Override
    public void add(LinkBean linkObj) {
        //连接对象
        Connection con = null;
        //SQL语句执行对象
        PreparedStatement ps = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //建立连接, localhost表示连接数据库所在服务器的IP地址，6789为连接端口
            //mydb表示连接mysql 数据库库名
            //characterEncoding=utf-8 设置编码集
            //root 表示登录mysql的用户名 lovo表示登录mysql的密码
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8"
            ,"root","lovo");

            //执行sql语句
            ps = con.prepareStatement("insert into t_link(l_name,l_birthday,l_sex,l_phone)"+ "values(?,?,?,?)");
            //填充占位符
            ps.setObject(1,linkObj.getName());
            ps.setObject(2,linkObj.getBirthday());
            ps.setObject(3,linkObj.getSex());
            ps.setObject(4,linkObj.getPhone());
            //更新数据库，将数据真正写入数据库，除了查询都必须有更新数据库。
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                //关闭连接
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void delate(int id) {
        Connection con =null;
        PreparedStatement ps =null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("delete from t_link where pk_linkId =?");
            ps.setObject(1,id);

            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void update(int id, String phone) {
    Connection con = null;
    PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8",
                    "root","lovo");

            ps = con.prepareStatement("update t_link set l_phone=? where pk_linkId=? ");
            ps.setObject(1,phone);
            ps.setObject(2,id);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<LinkBean> findAll() {
        List<LinkBean> list = new ArrayList<>();
        Connection con =null;
        PreparedStatement ps =null;
        ResultSet rs =null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8",
            "root","lovo");

            ps = con.prepareStatement("select * from t_link");
            //将查询结果，存放至结果集对象中
            rs = ps.executeQuery();
            //将结果集指针指向下一条记录，如果返回值为false，表示没有下一条记录，遍历结束
            while (rs.next()){
                LinkBean linkObj = new LinkBean();
                linkObj.setId(rs.getInt("pk_linkId"));
                linkObj.setName(rs.getString("l_name"));
                linkObj.setSex(rs.getString("l_sex"));
                linkObj.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
                linkObj.setPhone(rs.getString("l_phone"));

                list.add(linkObj);

            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public LinkBean findById(int id) {
        LinkBean linkBean = null;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_link where pk_linkId=? ");
            ps.setObject(1,id);
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
        if(rs.next()){
            linkBean = new LinkBean();
            linkBean.setId(rs.getInt("pk_linkId"));
            linkBean.setName(rs.getString("l_name"));
            linkBean.setSex(rs.getString("l_sex"));
            linkBean.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
            linkBean.setPhone(rs.getString("l_phone"));
        }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return linkBean;
    }

    @Override
    public List<LinkBean> findByName(String name) {
        List<LinkBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;


        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:6789/mydb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_link where l_name like ?");
            ps.setObject(1,"%"+name+"%");
            //将查询结果，存放至结果查询中
            rs = ps.executeQuery();
            while (rs.next()){
                LinkBean linkBean = new LinkBean();
                linkBean.setId(rs.getInt("pk_linkId"));
                linkBean.setName(rs.getString("l_name"));
                linkBean.setSex(rs.getString("l_sex"));
                linkBean.setBirthday(LocalDate.parse(rs.getString("l_birthday")));
                linkBean.setPhone(rs.getString("l_phone"));
                list.add(linkBean);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return list;
    }

    public static void main(String[] args) {
        ILinkDao dao = new LinkDaoImpl();
//        dao.add(new LinkBean("郑涛", "男",LocalDate.parse("1995-09-25"),"13596254865"));
//        dao.delate(5);
//        dao.update(6,"15369874567");
//        System.out.println(dao.findById(20));
        System.out.println(dao.findByName("赵"));
    }
}
